/**
 * 
 */
package fr.univ_tln.inf9.exaplanning.api.db;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Cette version de dataBaseManager
 * assure le lien entre l'application et la base de données à l'aide
 * de l'introspection et de l'annotation EntityInfo, elle n'est en
 * relation qu'avec le gestionnaire de session il ne faut pas chercher
 * à interagir avec.
 * @author emilien
 */
public class DataBaseManager {
	
	private static DataBaseManager instance = null;
	private static Connection con;
	private static String URL;
	private static String LOGIN;
	private static String PASSWORD;
	private static Map <Class, PreparedStatement> statementInserts;
	private static Map <Class, PreparedStatement> statementUpdates;
	private static Map <Class, PreparedStatement> statementInstanciates;
	private static Map <Class, PreparedStatement> statementCollections;
	
	// TODO: création de l'évènement dbFileConfigException
	/**
	 * @param configFileName Le fichier de configuration de la base
	 * @throws SQLException 
	 */
	private DataBaseManager(String configFileName) throws IOException, SQLException {
		InputStreamReader isr = null;
		BufferedReader buff = null;
		
		try {
			isr = new InputStreamReader(new FileInputStream(configFileName));
			buff = new BufferedReader(isr);

			String read = buff.readLine();
			while (read != null) {
			
				String var = read.split("=")[0];
				String value = read.split("=")[1];
			
				if (var.equals("URL")) 
					URL = value;
				else if (var.equals("LOGIN"))
					LOGIN = value;
				else if (var.equals("PASSWORD"))
					PASSWORD = value;

				read = buff.readLine();
			}
		} catch (IOException e) {
				throw e;
		} finally {
				buff.close();
				isr.close();
		}
		
		statementInserts = new HashMap<Class, PreparedStatement>();
		statementUpdates = new HashMap<Class, PreparedStatement>();
		statementInstanciates = new HashMap<Class, PreparedStatement>();
		statementCollections = new HashMap<Class, PreparedStatement>();
		con = DriverManager.getConnection(URL, LOGIN, PASSWORD);
	}
	
    public final synchronized static DataBaseManager getInstance() throws IOException, SQLException {
        if (instance == null) 
            instance = new DataBaseManager("config/db.cfg");
        return instance;
    }
    
    private static PreparedStatement createPreparedStatementInstanciate(Class c) throws SQLException {
		
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
    	String sqlTable = info.table();
    	String sqlKey = info.rows()[0];
    	
    	String sql = "SELECT * FROM "+sqlTable+" WHERE "+sqlKey+" = ?";
    	PreparedStatement result = con.prepareStatement(sql);
    	
		return result;
	}
    
    public List<String> getVariables(Class c, String key) throws SQLException {
		
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
    	PreparedStatement pstmt;
    	
    	if (!statementInstanciates.containsKey(c)) {
    		pstmt = createPreparedStatementInstanciate(c);
    		statementInstanciates.put(c, pstmt);
    	} else {
    		pstmt = statementInstanciates.get(c);
    	}
    	
    	pstmt.setString(1, key);
    	ResultSet rs = pstmt.executeQuery();
    	rs.next();
    	
    	List<String> results = new LinkedList<String>();
    	int nbRows = info.rows().length;
    	int i = 1;
    	while (i <= nbRows) {
    		results.add(rs.getString(i));
    		i++;
    	}
    	
    	return results;
    	
    }

	private PreparedStatement createPreparedStatementInsert(Class c) throws SQLException {
    	
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
    	List<String> rows = Arrays.asList(info.rows());
    	Iterator<String> itRows = rows.iterator();
    	
    	String sqlRows = "";
    	String sqlTable = info.table();
    	String sqlValues = "";
    	
    	while (itRows.hasNext()) {
    		String row = itRows.next();
    		if (itRows.hasNext()) {
    			sqlRows = sqlRows+row+", ";
    			sqlValues = sqlValues+"?, ";
    		} else {
    			sqlRows = sqlRows+row;
    			sqlValues = sqlValues+"?";
    		}
    	}
    	
    	String sql = "INSERT INTO "+sqlTable+"("+sqlRows+") VALUES("+sqlValues+")";
    	PreparedStatement result = con.prepareStatement(sql);
    	
    	return result;
    }
    
    private PreparedStatement createPreparedStatementUpdate(Class c) throws SQLException {
    	
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
    	List<String> rows = Arrays.asList(info.rows());
    	Iterator<String> itRows = rows.iterator();
    	
    	String sqlRows = "SET ";
    	String sqlKey = info.key();
    	String sqlTable = info.table();
    	
    	itRows.next();
    	while (itRows.hasNext()) {
    		String row = itRows.next();
    		if (itRows.hasNext()) {
    			sqlRows = sqlRows+row+" = ?, ";
    		} else {
    			sqlRows = sqlRows+row+" = ?";
    		}
    	}
    	
    	String sql = "UPDATE "+sqlTable+" "+sqlRows+" WHERE "+sqlKey+" = ?";
    	PreparedStatement result = con.prepareStatement(sql);
    	
    	return result;
    }
    
    /**
     * 
     * TODO: Gestion propre des erreurs
     * Insert dans la BD l'objet passé en paramètre
     * @throws NoSuchMethodException 
     * @throws SecurityException 
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     * @throws IllegalArgumentException 
     * @throws SQLException 
     */
    protected void create(AbstractEntity o) throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, SQLException {
    	
    	Class c = o.getClass();
    	PreparedStatement pstmt;
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);

    	if (!statementInserts.containsKey(o.getClass())) {
    		pstmt = createPreparedStatementInsert(o.getClass());
    		statementInserts.put(c, pstmt);
    	} else {
    		pstmt = statementInserts.get(c);
    	}
    
    	List<String> methods = Arrays.asList(info.methods());
    	Iterator<String> itMethods = methods.iterator();
    	
    	// Parcours des variables necessitant d'être sauvegardées
    	int i = 1;
    	while (itMethods.hasNext()) {
    		Method m = o.getClass().getMethod(itMethods.next(), null);
    		pstmt.setString(i, ""+m.invoke(o, null)+"");
    		i++;
    	}

    	pstmt.addBatch();
    }
    
    /**
     * @throws SQLException 
     * @throws NoSuchMethodException 
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws IllegalArgumentException 
     * 
     */
    protected void merge(AbstractEntity o) throws SQLException, IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
    	
    	Class c = o.getClass();
    	PreparedStatement pstmt;
    	EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
    	
    	if (!statementUpdates.containsKey(o.getClass())) {
    		pstmt = createPreparedStatementUpdate(c);
    		statementUpdates.put(c, pstmt);
    	} else {
    		pstmt = statementUpdates.get(c);
    	}
    	
    	List<String> methods = Arrays.asList(info.methods());
    	Iterator<String> itMethods = methods.iterator();
    	
    	String key = ""+c.getMethod(itMethods.next(), null).invoke(o, null);
    	
    	
    	int i = 1;
    	while (itMethods.hasNext()) {
    		Method m = c.getMethod(itMethods.next(), null);
    		pstmt.setString(i, ""+m.invoke(o, null));
    		i++;
    	}
    	
    	pstmt.setString(i, key);
    	pstmt.addBatch();
    	
    }
    
    protected void save() throws SQLException {
    	
    	Set<Class> keys = statementInserts.keySet();
    	Iterator<Class> it = keys.iterator();
    	while (it.hasNext()) {
    		statementInserts.get(it.next()).executeBatch();
    	}
    	
    	keys = statementUpdates.keySet();
    	it = keys.iterator();
    	while (it.hasNext()) {
    		statementUpdates.get(it.next()).executeBatch();
    	}
    }

	protected List<String> getCollection(Class c, String where, String whereValue) throws SQLException {
    	
		PreparedStatement pstmt;
		
		if (!statementCollections.containsKey(c)) {
    		pstmt = createPreparedStatementCollection(c, where);
    		statementCollections.put(c, pstmt);
    	} else {
    		pstmt = statementCollections.get(c);
    	}
		
		List<String> result = new LinkedList<String>();
		pstmt.setString(1, whereValue);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			result.add(rs.getString(1));
		}
		
		return result;
	}

	private PreparedStatement createPreparedStatementCollection(Class c, String where) throws SQLException {
		
		EntityInfo info = (EntityInfo) c.getAnnotation(EntityInfo.class);
		
		String sql = "SELECT "+info.rows()[0]+" FROM "+info.table()+" WHERE "+where+" = ?";
		PreparedStatement result = con.prepareStatement(sql);
		return result;
	}
}
